As mortgage interest rates continued to decline through
early 2021, I decided to see how much we could save by refinancing. Since I knew I would be shopping around from
different sources who offer different rates, closing costs, points, etc. I thought this would be a good time to
crank out another Shiny tool. The objectives of this tool
were as follows:
Objective #3 was really my main goal here. Pretty much as soon as I started looking I realized we should pull the trigger, which just left her to be convinced.
In this post I’m going to go over the calculations I built into the app. In the next post, I’ll go over the visuals I generated with those results and in the final post of this series I’ll summarize the Shiny app layout.
The only real financial calculation standing in my way was amortization, or the calculation of monthly payment amount given an interest rate, principal, and length of loan. There are a number of R packages that deal with financial calculations, including some that have built in amortization functions but I wanted to understand what was going on here so I went ahead and pulled the equation from wikipedia:
\[M = \frac{P * r}{(1 - (1 + r)^{-n})}\]
where:
This is easy enough to implement in R. I’ll check with some reasonable values, say $300,000 principal, 30 year term, 4.0% APR.
P <- 300000
r <- 4/100/12
n <- 30*12
M = (P*r)/(1-(1+r)^(-n))
M## [1] 1432.246That is exactly what I get when I check against a published amortization calculator, so I think I’m good to go.
As I think through my desired data structure for summary visuals, these are the measures I want to calculate for each month:
My resulting dataframe should then be a time series with a row for every month throughout the life of the loan and a column for each of the above measures. I’ll need to generate one for the original mortgage and one for the refinanced mortgage.
First, I need to calculate how much interest and principal I’m paying in the first month. It turns out calculating the monthly interest is as easy as multiplying the outstanding principal by the monthly interest rate. Then, the amount of my monthly payment which goes towards principal is simply my monthly payment (\(M\)) minus my monthly paid towards interest (\(mI\)). And finally to calculate the amount of principal remaining at the end of the month, I simply subtract the amount I paid in principal (\(mP\)) from the outstanding principal at the beginning of the month.
#monthly interest
mI <- P*r
#monthly principal
mP <- M - mI
#end of month loan balance
iP <- P - mP
cat(paste(
"Monthly Interest Paid =", mI,
"\nMonthly Principal Paid =", mP,
"\nEnd of Month Loan Balance =", iP
))## Monthly Interest Paid = 1000
## Monthly Principal Paid = 432.245886396361
## End of Month Loan Balance = 299567.754113604Ok, easy enough, but that was just for the first month. Now I’m going to extend it for each month throughout
the life of the loan. There is likely an elegant, vectorized solution to this but since it’s not an intensive
calculation I’m going to handle it in a good old fashioned for loop! I’m looping through each month of the
life of the loan, so for i in 1:n. Since the above calculations change with each month, and
therefore each loop, I have to initialize them outside of the loop and then update inside the loop. I’m also
initializing a dataframe to store the values and then adding a row in each loop.
library(tidyverse)
library(lubridate)
#initialize vars
origDF <- tibble()
iP <- P
iP_paid <- 0
iI_paid <- 0
itotal_paid <- 0
#start time series at 2019-06-01
it <- ymd("2019-06-01")
#loop through months and generate time series dataframe
for (i in 1:n) {
#monthly interest
mI <- iP * r
#monthly principal
mP <- M - mI
#ending loan balance
iP <- iP - mP
#data rows
iDF <- tibble(
date = it, payment = M,
principal_payment = mP, interest_payment = mI,
ending_balance = iP,
principal_paid = iP_paid + mP,
interest_paid = iI_paid + mI,
total_paid = itotal_paid + mP + mI
)
origDF <- origDF %>% bind_rows(iDF)
#update vars
it <- it + months(1)
iP_paid <- iP_paid + mP
iI_paid <- iI_paid + mI
itotal_paid <- itotal_paid + mP + mI
}
str(origDF)## tibble [360 × 8] (S3: tbl_df/tbl/data.frame)
## $ date : Date[1:360], format: "2019-06-01" "2019-07-01" ...
## $ payment : num [1:360] 1432 1432 1432 1432 1432 ...
## $ principal_payment: num [1:360] 432 434 435 437 438 ...
## $ interest_payment : num [1:360] 1000 999 997 996 994 ...
## $ ending_balance : num [1:360] 299568 299134 298699 298262 297824 ...
## $ principal_paid : num [1:360] 432 866 1301 1738 2176 ...
## $ interest_paid : num [1:360] 1000 1999 2996 3991 4986 ...
## $ total_paid : num [1:360] 1432 2864 4297 5729 7161 ...And there we have it - the dataframe I was looking for, including all column variables I defined above and a row for each month of the loan.
Before generating the same results for the refinanced mortgage, I need to determine a few values first. For
starters, the principal will be different, since the refinanced mortgage will take over where the original
mortgage left off and so I’ll need to pull that from the ending_balance column of
origDF. Similarly, my initialized values of iP_paid, iI_paid, and
itotal_paid are no longer zero since I’ve already paid towards the original loan. Below, I’m
going to assume I found a good deal on a refinance and my rate dropped to 2.5%. I’m keeping the same 30 year
term and the new refinance is set to take over a little over 2 years after the original mortgage started,
2021-07-01. I’m also going to calculate my new monthly payment.
#refinance mortgage start date
t0 <- ymd("2021-07-01")
#derive principal from origDF
P <- origDF %>%
filter(date == t0 - months(1)) %>%
pull(ending_balance)
#derive prinicpal paid from origDF
P0 <- origDF %>%
filter(date == t0 - months(1)) %>%
pull(principal_paid)
#derive interest paid from origDF
I0 <- origDF %>%
filter(date == t0 - months(1)) %>%
pull(interest_paid)
#monthly interest rate
r <- 2.5/100/12
#monthly payment
M <- P * r / (1 - (1 + r)^(-n))Now before I generate refiDF using these new values for the refinanced mortgage, I need to
introduce one final issue. In order to get a complete picture of the financial comparison, I need to take into
account all up-front, one time costs. Closing costs are straight-forward, they’re simply the expenses you pay
for the services of the lending bank during the transaction. Mortgage points are fees paid directly to the
lender at closing in exchange for a reduced interest rate. In some cases the bank will require you to pay a
set amount of points up front and in some cases they will give you an option to pay points to reduce the
interest rate. I’m going to pull points out as a separate input so the user can tinker and get a good feel for
how they affect the resulting refinanced mortgage. Points are calculated as a percentage of the total
principal, so I’ll simply multiply points by the refinance principal derived above.
#up-front closing costs
close_cost <- 4000
#points to be paid up front
points <- 2
#sum up all additional costs
add_costs <- close_cost + points/100*PNow I will initialize my refinance values using all the variables I derived above and generate
refiDF with the same for loop I used previously.
#initialize using refinance values
refiDF <- tibble()
iP <- P
iP_paid <- P0
iI_paid <- I0
it <- t0
itotal_paid <- add_costs + P0 + I0
#loop through months and generate time series
for (i in 1:n) {
#monthly interest
mI <- iP * r
#monthly principal
mP <- M - mI
#ending loan balance
iP <- iP - mP
#data rows
iDF <- tibble(
date = it, payment = M,
principal_payment = mP, interest_payment = mI,
ending_balance = iP,
principal_paid = iP_paid + mP,
interest_paid = iI_paid + mI,
total_paid = itotal_paid + mP + mI
)
refiDF <- refiDF %>% bind_rows(iDF)
it <- it + months(1)
iP_paid <- iP_paid + mP
iI_paid <- iI_paid + mI
itotal_paid <- itotal_paid + mP + mI
}
str(refiDF)## tibble [360 × 8] (S3: tbl_df/tbl/data.frame)
## $ date : Date[1:360], format: "2021-07-01" "2021-08-01" ...
## $ payment : num [1:360] 1141 1141 1141 1141 1141 ...
## $ principal_payment: num [1:360] 539 540 542 543 544 ...
## $ interest_payment : num [1:360] 602 600 599 598 597 ...
## $ ending_balance : num [1:360] 288211 287671 287129 286586 286042 ...
## $ principal_paid : num [1:360] 11789 12329 12871 13414 13958 ...
## $ interest_paid : num [1:360] 25158 25759 26358 26956 27553 ...
## $ total_paid : num [1:360] 46722 47863 49004 50145 51286 ...That’s it! I’ve calculated everything I need to analyze this problem in detail. Now it’s time to move on to the fun part, making the visuals. I’ll cover that in the next post. Sneak peek below - it looks like I’m going to save a ton in interest, even with those hefty up-front costs.